﻿Imports System.IO
Imports System.Text.RegularExpressions
Imports Microsoft.Office.Interop.Excel

Public Class CacheOperation
    Implements IDisposable
    Friend Form1
    Friend InputParser
    Public Shared types As New HashSet(Of String)({"Вакси_грузовая", "Вакси_легковая", _
                                                     "Вася", "Гранат", "Кама_Алтай", "Кама_Кама", _
                                                     "Мотопарк", "Олег", "Опт", "Покрышкино", _
                                                     "Римбат", "СтартШина", "Шате_М", "Белшинторг", _
                                                   "Диски_Опт", "Диски_Гранат", "Диски_Покрышкино"})
    Protected Friend cachedir As DirectoryInfo
    Protected Friend cachepath As String
    Private Xl As Application
    Private texterrfile As String
    Private textfile As String
    Private succs_global As List(Of SuccObject)
    Private succs_fromcodes_global As List(Of SuccObject)
    Private err_global As List(Of String)
    Sub Dispose() Implements IDisposable.Dispose
        Try
            Xl.Quit()
        Catch ex As Exception
        Finally
            Xl = Nothing
        End Try
    End Sub
    Sub RebuildCacheList()
        My.Forms.Form1.CachedTypes.Clear()
        For Each cfile As FileInfo In cachedir.EnumerateFiles("*.csv")
            Dim n As String = Regex.Replace(cfile.Name, "^(.*)\.csv$", "$1")
            Dim d As Date = cfile.LastWriteTime
            If (types.Contains(n)) Then
                My.Forms.Form1.CachedTypes.Add(New CachedTypeObject(n, d.ToString("dd.MM.yy")))
            End If
        Next cfile
    End Sub
    Sub New()
        succs_fromcodes_global = New List(Of SuccObject)
        My.Forms.Form1.TypesBindingSource.DataSource = types
        cachepath = Directory.GetCurrentDirectory() + "\cache\"
        cachedir = Directory.CreateDirectory(cachepath)
        RebuildCacheList()

    End Sub
    Public Sub commit()

        Dim processed_set As New HashSet(Of String)

        Dim workdir As String = Directory.GetCurrentDirectory()
        Dim temp As String = Path.GetTempFileName
        Dim template = workdir + Path.DirectorySeparatorChar + "template.xlt"
        Xl.DisplayAlerts = False
        Dim resWB As Workbook
        resWB = Xl.Workbooks.Open(template)




        Dim linecount As Integer = 0
        Dim discs As New List(Of String)
        For Each f As FileInfo In cachedir.EnumerateFiles
            If Regex.IsMatch(f.Name, "^Диски") Then
                discs.AddRange(File.ReadAllLines(f.FullName, Encoding1251))
            Else
                Dim s As String() = File.ReadAllLines(f.FullName, Encoding1251)
                linecount = linecount + s.Count
                File.AppendAllLines(temp, s, Encoding1251)
            End If
        Next f
        If linecount <> 0 Then
            Dim allWB As Workbook = Xl.Workbooks.Open(temp, Format:=2)
            Dim src As Worksheet = allWB.ActiveSheet ' Format:=2 means commas (,) as delimiters, see msdn

            succs_global = extractText(src, False)


            Dim res As Worksheet = resWB.Sheets(RESULT_SHEET_NAME_LIGHT_TIRES)
            Dim res_heavy As Worksheet = resWB.Worksheets(RESULT_SHEET_NAME_HEAVY_TIRES)
            
            Dim r As Range = src.UsedRange
            Dim count As Integer = src.Cells.CurrentRegion.Rows.Count

            r.Sort(Key1:=r(1, 16), Order1:=XlSortOrder.xlAscending, _
                Header:=XlYesNoGuess.xlNo, MatchCase:=False, _
                Orientation:=XlSortOrientation.xlSortColumns, _
                DataOption1:=XlSortDataOption.xlSortNormal, _
                DataOption2:=XlSortDataOption.xlSortNormal, _
                DataOption3:=XlSortDataOption.xlSortNormal)


            Dim heavyrange As Range = getColumn(src, 16, count).Find(What:="легковая", _
                LookAt:=XlLookAt.xlPart, SearchOrder:=XlSearchOrder.xlByRows, SearchDirection:=XlSearchDirection.xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If heavyrange Is Nothing Then
                src.Range(src.Cells(1, 1), src.Cells(count, RESULT_AVAIL_COLUMN)).Copy()
                res_heavy.Cells(2, 1).PasteSpecial(XlPasteType.xlPasteValues)
            Else
                Dim heavy As Integer = heavyrange.Row
                If (heavy > 2) Then
                    src.Range(src.Cells(2, 1), src.Cells(heavy - 1, RESULT_AVAIL_COLUMN)).Copy()
                    res_heavy.Cells(2, 1).PasteSpecial(XlPasteType.xlPasteValues)

                    src.Range(src.Cells(heavy, 1), src.Cells(count, RESULT_AVAIL_COLUMN)).Copy()
                    res.Cells(2, 1).PasteSpecial(XlPasteType.xlPasteValues)
                Else
                    src.Range(src.Cells(1, 1), src.Cells(count, RESULT_AVAIL_COLUMN)).Copy()
                    res.Cells(2, 1).PasteSpecial(XlPasteType.xlPasteValues)
                End If
            End If
            

            'hide away
            res.Columns(Variables.RESULT_AVAIL_COLUMN).Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)
            res_heavy.Columns(Variables.RESULT_AVAIL_COLUMN).Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)
            res.Columns(Variables.RESULT_TIRETYPE_COLUMN).Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)
            res_heavy.Columns(Variables.RESULT_TIRETYPE_COLUMN).Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)

            For Each w As Worksheet In {res, res_heavy}
                count = w.Cells.CurrentRegion.Rows.Count
                'ExcelOps.getColumn(w, 14, count + 1, 2).FormulaR1C1 = "=RC[-3]-RC[-4]" ' 14.delta formula
                'ExcelOps.getColumn(w, 12, count + 1, 2).FormulaR1C1 = "=RC[-1]*" + DoubleToExcelString(NDS_COEFF) + "*" + BYR_USD_CONV.ToString ' 12.opt+NDS formula
                'ExcelOps.getColumn(w, 17, count + 1, 2).FormulaR1C1 = "=RC11*" + BYR_USD_CONV.ToString
                'ExcelOps.getColumn(w, 18, count + 1, 2).FormulaR1C1 = "=RC10*" + BYR_USD_CONV.ToString

                ExcelOps.getColumn(w, 12, count + 1, 2).FormulaR1C1 = "=IF( RC[-1] > 50000 , RC[-1]*" + DoubleToExcelString(NDS_COEFF) + " ,  RC[-1]*" + DoubleToExcelString(NDS_COEFF) + "*" + BYR_USD_CONV.ToString + ")"

                ExcelOps.getColumn(w, 17, count + 1, 2).FormulaR1C1 = "=IF( RC11 > 50000 , RC11 , RC11*" + BYR_USD_CONV.ToString + ")"
                ExcelOps.getColumn(w, 18, count + 1, 2).FormulaR1C1 = "=IF( RC10 > 50000 , RC10 , RC10*" + BYR_USD_CONV.ToString + ")"

                ExcelOps.getColumn(w, 14, count + 1, 2).FormulaR1C1 = "=RC17-RC18"
            Next w
            
            setSorting(res, 12)
            setSorting(res_heavy, 12)

            ' розн в шинах
            'ExcelOps.getColumn(res, 17).FormulaR1C1 = "=RC11*" + BYR_USD_CONV.ToString
            'ExcelOps.getColumn(res_heavy, 17).FormulaR1C1 = "=RC11*" + BYR_USD_CONV.ToString

            ExcelOps.moveColumn(res, 17, 12)
            ExcelOps.moveColumn(res_heavy, 17, 12)
            ExcelOps.moveColumn(res, 18, 11)
            ExcelOps.moveColumn(res_heavy, 18, 11)

            'ExcelOps.getColumn(res, 11).Hidden = True

            'ExcelOps.getColumn(res, 17, 1, 1).Value = "Розн"
            'ExcelOps.getColumn(res_heavy, 17, 1, 1).Value = "Розн"

            src = Nothing
            res = Nothing
            res_heavy = Nothing
            allWB.Close()
            allWB = Nothing
        End If


            If discs.Count > 0 Then
                commitDiscs(resWB, discs)
            End If

            succs_global.AddRange(succs_fromcodes_global)


            Try
                File.AppendAllLines(texterrfile, err_global, Encoding1251)
            Catch exc As IOException
                MsgBox("Ошибка ввода-вывода при попытке записи в файл:" + texterrfile)
            End Try

            Try
                If Not textfile Is Nothing Then
                    File.AppendAllLines(textfile, succOut(succs_global))
                End If
            Catch exc As IOException
                MsgBox("Ошибка ввода-вывода при попытке записи в файл:" + textfile)
            End Try

            With My.Forms.Form1.ExcelSaveDialog
                '.Reset()
                .FileName = EXPORT_EXCEL_DEFAULT_FILENAME
                If (.ShowDialog() = DialogResult.OK) Then
                    Dim fileformat As Object
                    If (Regex.IsMatch(.FileName, "\.xls$")) Then
                        fileformat = XlFileFormat.xlExcel8
                    Else
                        fileformat = XlFileFormat.xlExcel12
                    End If
                    resWB.SaveAs(Filename:=.FileName, FileFormat:=fileformat)
                    resWB.Close()
                    Xl.DisplayAlerts = True
                    Xl.ScreenUpdating = True
                    Xl.Quit()
                Else
                    Xl.ScreenUpdating = True
                    Xl.DisplayAlerts = True
                    Xl.Visible = True
                    Xl.UserControl = True
                End If
            End With

            resWB = Nothing
            Xl = Nothing

            File.Delete(temp)
    End Sub
    Sub initExcel()
        Xl = New Application
        Xl.DisplayAlerts = False
        Xl.ScreenUpdating = False
        Xl.Visible = False
    End Sub
    Sub cache(textfile As String, texterrfile As String)
        initExcel()

        Dim cached As New SortedDictionary(Of String, CachedTypeObject)
        Me.textfile = textfile
        Me.texterrfile = texterrfile

        Dim inp_parser As InputParser
        inp_parser = New InputParser(cachepath, Xl)
        With My.Forms.Form1
            For Each o As CachedTypeObject In .CachedTypes ' populate dict 
                Try
                    cached.Add(o.type, o)
                Catch x As Exception
                End Try
            Next
            For Each item As GridObject In .CurrentItems

                If (cached.ContainsKey(item.type)) Then ' if type is in list of cached, override - delete cache and remove from list
                    .CachedTypes.Remove(cached.Item(item.type))
                    Try
                        cachedir.GetFiles(item.type + ".csv").Single.Delete()
                    Catch x As Exception
                    End Try
                End If

                If (item.path Is Nothing Or item.path = "") Then
                    MsgBox("Файл прайса для типа " + item.type + "не существует")
                    Exit Sub
                End If

                If Not (File.Exists(item.path)) Then
                    MsgBox("Файл " + item.path + " не существует")
                    Exit Sub
                End If

                If Not (item.matchfile Is Nothing Or item.matchfile = "") Then
                    If Not File.Exists(item.matchfile) Then
                        MsgBox("Файл " + item.matchfile + " не существует")
                        Exit Sub
                    End If
                End If

                inp_parser.parse(item) ' insert new one in cache

            Next item
        End With
        RebuildCacheList()

        err_global = inp_parser.err_global
        succs_fromcodes_global = inp_parser.succs_global_fromcodes
        inp_parser.kill()


        inp_parser = Nothing
    End Sub

    Sub clearcache()
        For Each f As FileInfo In cachedir.GetFiles()
            Try
                f.Delete()
            Catch ex As IOException
                MsgBox("Невозможно удалить файл из кэша - " + f.FullName + ".")
                Exit Sub
            End Try
        Next

        RebuildCacheList()
    End Sub

    Private Function extractText(src As Worksheet, discs As Boolean) As List(Of SuccObject)
        Dim sitecode, price, avail, quantity, type
        Dim i As Integer
        Dim l As New List(Of SuccObject)
        Dim count As Integer = src.Columns(1).CurrentRegion.Rows.Count
        sitecode = ExcelOps.getColumn(src, RESULT_SITECODE_COLUMN, count).Value
        avail = ExcelOps.getColumn(src, RESULT_AVAIL_COLUMN, count).Value

        If discs Then
            quantity = ExcelOps.getColumn(src, 9, count).Value
            price = ExcelOps.getColumn(src, 13, count).Value
            type = ExcelOps.getColumn(src, 16, count).Value
        Else
            quantity = ExcelOps.getColumn(src, 9, count).Value
            price = ExcelOps.getColumn(src, 11, count).Value
            type = ExcelOps.getColumn(src, 13, count).Value
        End If

        For i = 1 To count
            If Not sitecode(i, 1) Is Nothing Then
                If sitecode(i, 1).ToString <> sitecode_null Then
                    l.Add(New SuccObject(sitecode(i, 1) _
                                         , Boolean.Parse(avail(i, 1)) _
                                         , MatchParser.getPrice(price(i, 1)) _
                                         , MatchParser.getQuantity(quantity(i, 1)), type(i, 1)))
                End If
            End If
        Next
        extractText = l
    End Function

    Private Sub commitDiscs(ByRef resWB As Workbook, ByRef collected As List(Of String))
        Dim temp As String = Path.GetTempFileName
        Dim res As Worksheet = resWB.Sheets(RESULT_SHEET_NAME_DISCS)
        File.WriteAllLines(temp, collected, Encoding1251)
        Dim discsWB As Workbook = Xl.Workbooks.Open(temp, Format:=2)
        Dim src As Worksheet = discsWB.ActiveSheet ' Format:=2 means commas (,) as delimiters, see msdn
        src.UsedRange.Copy()
        res.Cells(2, 1).PasteSpecial(XlPasteType.xlPasteValues)

        If succs_global Is Nothing Then
            succs_global = extractText(src, True)
        Else
            succs_global.AddRange(extractText(src, True))
        End If

        setSorting(res, 13)

        'res.Columns(Variables.RESULT_AVAIL_COLUMN).Delete(Shift:=XlDeleteShiftDirection.xlShiftToLeft)

        ExcelOps.getColumn(res, 14, startrow:=2).FormulaR1C1 = "=RC[-1]-RC[-2]" ' 14.delta formula
        'ExcelOps.getColumn(res, 14, startrow:=2).FormulaR1C1 = "=RC[-1]/" + Variables.BYR_USD_CONV.ToString + "-RC[-2]" ' 14.delta formula, retail in BYR
        'ExcelOps.getColumn(res, RESULT_DISCS_OPTxCOURSE_COLUMN, startrow:=2).FormulaR1C1 = "=RC13*" + BYR_USD_CONV.ToString ' 18.opt*BYR_USD_CONV
        ExcelOps.getColumn(res, RESULT_DISCS_OPTxCOURSE_COLUMN, startrow:=2).FormulaR1C1 = "=RC13" ' + BYR_USD_CONV.ToString ' 18.opt*BYR_USD_CONV

        ExcelOps.getColumn(res, 20, startrow:=2).FormulaR1C1 = "=IF( RC11 > 50000 , RC11 , RC11*" + BYR_USD_CONV.ToString + ")"
        ExcelOps.getColumn(res, 21, startrow:=2).FormulaR1C1 = "=IF( RC12 > 50000 , RC12 , RC12*" + BYR_USD_CONV.ToString + ")"
        ExcelOps.getColumn(res, 22, startrow:=2).FormulaR1C1 = "=IF( RC13 > 50000 , RC13 , RC13*" + BYR_USD_CONV.ToString + ")"
        ExcelOps.getColumn(res, 23, startrow:=2).FormulaR1C1 = "=IF( RC19 > 50000 , RC19*" + DoubleToExcelString(NDS_COEFF) + " , RC19*" + BYR_USD_CONV.ToString + "*" + DoubleToExcelString(NDS_COEFF) + ")"
        ExcelOps.getColumn(res, 24, startrow:=2).FormulaR1C1 = "=IF( RC13 > 50000 , RC14 , RC14*" + BYR_USD_CONV.ToString + ")"

        'thrash the template
        ExcelOps.moveColumn(res, 18, 14)
        ExcelOps.moveColumn(res, 19, 15)

        'ExcelOps.moveColumn(res, 20, 11)
        'ExcelOps.moveColumn(res, 21, 12)
        'ExcelOps.moveColumn(res, 22, 13)
        'ExcelOps.moveColumn(res, 23, 14)
        'ExcelOps.moveColumn(res, 24, 15)

        src = Nothing
        res = Nothing
        discsWB.Close()
        discsWB = Nothing
        File.Delete(temp)
    End Sub
    Private Sub setSorting(res As Worksheet, column As Integer)
        'default price sorting
        res.AutoFilter.Sort.SortFields.Add(Key:=res.Cells(1, column), _
                                           SortOn:=XlSortOn.xlSortOnValues, _
                                           Order:=XlSortOrder.xlAscending, _
                                           DataOption:=XlSortDataOption.xlSortNormal)
        'commence
        With res.AutoFilter.Sort
            .Header = XlYesNoGuess.xlYes
            .MatchCase = False
            .Orientation = XlSortOrientation.xlSortColumns
            .SortMethod = XlSortMethod.xlPinYin
            .Apply()
        End With
    End Sub
End Class
